import json
from glob import iglob
from feapder.utils.log import log
from feapder.utils.tools import get_current_date
from snowflake_id import IdWorker


worker = IdWorker(1, 2, 0)


def extract_key(data, keys):
    res = []
    for item in data:
        temp = []
        for key in keys:
            try:
                temp.append({key: item[key]})
            except KeyError:
                # raise KeyError(key)
                continue
        res.append(temp)
    if len(res) == 1:
        return res[0]
    return res


# 在网页中直接使用开始
print('*' * 50 + '网页中直接使用' + '*' * 50)


def read_file(fp):
    fr = open(fp, 'r', encoding='utf-8').read()
    return json.loads(fr)


def get_file_path_list(fp, postfix):
    """
    递归搜索文件
    :param fp: 路径
    :param postfix: 文件后缀
    :return:
    """
    file_path_list = [i for i in iglob(f'{fp}/**/*{postfix}', recursive=True)]
    return file_path_list


def get_data(dict_obj, path):
    data = {'sourcecode_path': path,
            'id': worker.get_id()}

    PDB_ID = dict_obj['@datablockName']
    data['PDB_ID'] = PDB_ID
    data['create_by'] = 'xubin@%'
    data['update_by'] = 'xubin@%'
    data['create_on'] = get_current_date()
    data['update_on'] = get_current_date()
    data['batch_id'] = '4539023048687226881'

    try:
        data_dict = dict_obj['PDBx:citationCategory']['PDBx:citation']
        key_list = ['PDBx:journal_abbrev',
                    'PDBx:journal_id_ISSN',
                    'PDBx:pdbx_database_id_DOI',
                    'PDBx:pdbx_database_id_PubMed',
                    'PDBx:title',
                    'PDBx:year']

        # print('citationCategory', extract_key(data_dict, key_list))
        data['citationCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:citation_authorCategory']['PDBx:citation_author']
        key_list = ['@name', '@ordinal']
        # print('citation_authorCategory', extract_key(data_dict, key_list))
        data['citation_authorCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:entityCategory']['PDBx:entity']
        key_list = ['PDBx:pdbx_description']
        # print('entityCategory', extract_key(data_dict, key_list))
        data['entityCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:entity_name_comCategory']['PDBx:entity_name_com']
        key_list = ['@entity_id', 'PDBx:name']
        # print('entity_name_comCategory', extract_key(data_dict, key_list))
        data['entity_name_comCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:entity_polyCategory']['PDBx:entity_poly']
        key_list = ['PDBx:pdbx_seq_one_letter_code', 'PDBx:pdbx_strand_id', 'PDBx:type']
        # print('entity_polyCategory', extract_key(data_dict, key_list))
        data['entity_polyCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:entity_src_genCategory']['PDBx:entity_src_gen']
        key_list = ['PDBx:gene_src_common_name',
                    'PDBx:pdbx_beg_seq_num',
                    'PDBx:pdbx_end_seq_num',
                    'PDBx:pdbx_gene_src_gene',
                    'PDBx:pdbx_gene_src_ncbi_taxonomy_id',
                    'PDBx:pdbx_gene_src_scientific_name',
                    'PDBx:pdbx_host_org_cell_line',
                    'PDBx:pdbx_host_org_ncbi_taxonomy_id',
                    'PDBx:pdbx_host_org_scientific_name']
        # print('entity_src_genCategory', extract_key(data_dict, key_list))
        data['entity_src_genCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:exptlCategory']['PDBx:exptl']
        key_list = ['@method']
        # print('exptlCategory', extract_key(data_dict, key_list))
        data['exptlCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:exptl_crystal_growCategory']['PDBx:exptl_crystal_grow']
        key_list = ['PDBx:method', 'PDBx:pH', 'PDBx:pdbx_details', 'PDBx:temp']
        # print('exptl_crystal_growCategory', extract_key(data_dict, key_list))
        data['exptl_crystal_growCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:pdbx_poly_seq_schemeCategory']['PDBx:pdbx_poly_seq_scheme']
        key_list = [
            '@asym_id',
            '@mon_id',
            '@seq_id',
            'PDBx:ndb_seq_num',
            'PDBx:pdb_seq_num',
            'PDBx:pdb_strand_id',
        ]
        # print('pdbx_poly_seq_schemeCategory', extract_key(data_dict, key_list))
        data['pdbx_poly_seq_schemeCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass
    try:
        data_dict = dict_obj['PDBx:struct_conf_typeCategory']['PDBx:struct_conf_type']
        key_list = ['@id']
        # print('struct_conf_typeCategory', extract_key(data_dict, key_list))
        data['struct_conf_typeCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:struct_connCategory']['PDBx:struct_conn']
        key_list = ['@id', 'PDBx:pdbx_role', 'PDBx:ptnr1_auth_seq_id', 'PDBx:ptnr2_auth_seq_id']
        data['struct_connCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:structCategory']['PDBx:struct']
        key_list = ['PDBx:title']
        # print('structCategory', extract_key(data_dict, key_list))
        data['structCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:struct_confCategory']['PDBx:struct_conf']
        key_list = ['PDBx:beg_auth_comp_id',
                    'PDBx:beg_auth_seq_id',
                    'PDBx:conf_type_id',
                    'PDBx:end_auth_comp_id',
                    'PDBx:end_auth_seq_id',
                    'PDBx:pdbx_PDB_helix_length']
        # print('struct_confCategory', extract_key(data_dict, key_list))
        data['struct_confCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:struct_refCategory']['PDBx:struct_ref']
        key_list = ['PDBx:db_code',
                    'PDBx:db_name',
                    'PDBx:entity_id',
                    'PDBx:pdbx_align_begin',
                    'PDBx:pdbx_db_accession',
                    'PDBx:pdbx_seq_one_letter_code']
        # print('struct_refCategory', extract_key(data_dict, key_list))
        data['struct_refCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:struct_ref_seqCategory']['PDBx:struct_ref_seq']
        key_list = ['PDBx:db_align_beg',
                    'PDBx:db_align_end',
                    'PDBx:pdbx_auth_seq_align_beg',
                    'PDBx:pdbx_auth_seq_align_end',
                    'PDBx:seq_align_beg',
                    'PDBx:seq_align_end']
        # print('struct_ref_seqCategory', extract_key(data_dict, key_list))
        data['struct_ref_seqCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass
    try:
        data_dict = dict_obj['PDBx:struct_ref_seq_difCategory']['PDBx:struct_ref_seq_dif']
        key_list = ['PDBx:details',
                    'PDBx:mon_id',
                    'PDBx:pdbx_auth_seq_num'
                    ]
        # print('struct_ref_seq_difCategory', extract_key(data_dict, key_list))
        data['struct_ref_seq_difCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:struct_sheet_rangeCategory']['PDBx:struct_sheet_range']
        key_list = ['PDBx:beg_auth_comp_id',
                    'PDBx:beg_auth_seq_id',
                    'PDBx:end_auth_comp_id',
                    'PDBx:end_auth_seq_id'
                    ]
        # print('struct_sheet_rangeCategory', extract_key(data_dict, key_list))
        data['struct_sheet_rangeCategory'] = extract_key(data_dict, key_list)
    except KeyError:
        pass

    try:
        data_dict = dict_obj['PDBx:struct_keywordsCategory']['PDBx:struct_keywords']
        key_list = ['@entry_id',
                    'PDBx:pdbx_keywords',
                    ]
        data['struct_keywordsCategory'] = extract_key(data_dict, key_list)

    except KeyError:
        pass

    return data


print('*' * 50 + '网页中直接使用' + '*' * 50)

# 在网页中直接使用结束
#
#
# data_dict = dict_obj['PDBx:citationCategory']['PDBx:citation']
# key_list = ['@id',
#             'PDBx:journal_abbrev',
#             'PDBx:journal_id_ISSN',
#             'PDBx:pdbx_database_id_DOI',
#             'PDBx:pdbx_database_id_PubMed',
#             'PDBx:title',
#             'PDBx:year']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:citation_authorCategory']['PDBx:citation_author']
# key_list = ['@name', '@ordinal']
# print(extract_key(data_dict, key_list))
# data_dict = dict_obj['PDBx:entityCategory']['PDBx:entity']
# key_list = ['@id',
#             'PDBx:formula_weight',
#             'PDBx:pdbx_description',
#             'PDBx:pdbx_number_of_molecules',
#             'PDBx:src_method',
#             'PDBx:type',
#             ]
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:entity_name_comCategory']['PDBx:entity_name_com']
# key_list = ['@entity_id', 'PDBx:name']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:entity_polyCategory']['PDBx:entity_poly']
# key_list = ['@entity_id',
#             'PDBx:pdbx_seq_one_letter_code',
#             'PDBx:pdbx_seq_one_letter_code_can',
#             'PDBx:pdbx_strand_id',
#             'PDBx:type']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:entity_src_genCategory']['PDBx:entity_src_gen']
# key_list = ['@entity_id',
#             '@pdbx_src_id',
#             'PDBx:gene_src_common_name',
#             'PDBx:pdbx_gene_src_gene',
#             'PDBx:pdbx_gene_src_ncbi_taxonomy_id',
#             'PDBx:pdbx_gene_src_scientific_name',
#             'PDBx:pdbx_host_org_ncbi_taxonomy_id',
#             'PDBx:pdbx_host_org_scientific_name']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:exptlCategory']['PDBx:exptl']
# key_list = ['@method']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:exptl_crystalCategory']['PDBx:exptl_crystal']
# key_list = ['@id', 'PDBx:density_Matthews', 'PDBx:density_percent_sol']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:chem_compCategory']['PDBx:chem_comp']
# key_list = ['@id']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:entity_poly_seqCategory']['PDBx:entity_poly_seq']
# key_list = ['@entity_id', '@mon_id', '@num', 'PDBx:hetero']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:pdbx_poly_seq_schemeCategory']['PDBx:pdbx_poly_seq_scheme']
# key_list = ['@asym_id', '@entity_id', '@mon_id', '@seq_id']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:struct_conf_typeCategory']['PDBx:struct_conf_type']
# key_list = ['@id']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:structCategory']['PDBx:struct']
# key_list = ['PDBx:title']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:struct_confCategory']['PDBx:struct_conf']
# key_list = ['@id', 'PDBx:beg_auth_asym_id',
#             'PDBx:beg_auth_comp_id',
#             'PDBx:beg_auth_seq_id',
#             'PDBx:beg_label_asym_id',
#             'PDBx:beg_label_comp_id',
#             'PDBx:beg_label_seq_id',
#             'PDBx:conf_type_id',
#             'PDBx:end_auth_asym_id',
#             'PDBx:end_auth_comp_id',
#             'PDBx:end_auth_seq_id',
#             'PDBx:end_label_asym_id',
#             'PDBx:end_label_comp_id',
#             'PDBx:end_label_seq_id',
#             'PDBx:pdbx_PDB_helix_class',
#             'PDBx:pdbx_PDB_helix_id',
#             'PDBx:pdbx_PDB_helix_length',
#             ]
# print(extract_key(data_dict, key_list))
#
# struct_conn = dict_obj['PDBx:struct_connCategory']['PDBx:struct_conn']
# print(struct_conn)
#
# data_dict = dict_obj['PDBx:struct_refCategory']['PDBx:struct_ref']
# key_list = ['@id',
#             'PDBx:db_code',
#             'PDBx:db_name',
#             'PDBx:entity_id',
#             'PDBx:pdbx_align_begin',
#             'PDBx:pdbx_db_accession',
#             'PDBx:pdbx_seq_one_letter_code',
#             ]
# print(extract_key(data_dict, key_list))
#
# struct_ref_seq = dict_obj['PDBx:struct_ref_seqCategory']['PDBx:struct_ref_seq']
# print(struct_ref_seq)
#
# struct_ref_seq_dif = dict_obj['PDBx:struct_ref_seq_difCategory']['PDBx:struct_ref_seq_dif']
# print(struct_ref_seq_dif)
#
# struct_sheet = dict_obj['PDBx:struct_sheetCategory']['PDBx:struct_sheet']
# print(struct_sheet)
#
# struct_sheet_range = dict_obj['PDBx:struct_sheet_rangeCategory']['PDBx:struct_sheet_range']
# print(struct_sheet_range)
#
# struct_site = dict_obj['PDBx:struct_siteCategory']['PDBx:struct_site']
# print(struct_site)
# data_dict = dict_obj['PDBx:chem_compCategory']['PDBx:chem_comp']
# key_list = ['@id']
# print(extract_key(data_dict, key_list))
#
# data_dict = dict_obj['PDBx:struct_asymCategory']['PDBx:struct_asym']
# key_list = ['@id']
# print(extract_key(data_dict, key_list))
#
#
# data_dict = dict_obj['PDBx:atom_siteCategory']['PDBx:atom_site']
# key_list = ['@id',
#             'PDBx:auth_asym_id',
#             'PDBx:auth_atom_id',
#             'PDBx:auth_comp_id',
#             'PDBx:auth_seq_id',
#             'PDBx:label_alt_id',
#             'PDBx:label_asym_id',
#             ]
# print(extract_key(data_dict, key_list))
from feapder.db.mysqldb import MysqlDB

MYSQL_IP = "192.168.1.99"
MYSQL_PORT = 3306
MYSQL_DB = "DomesticAlternative"
MYSQL_USER_NAME = "xubin"
MYSQL_USER_PASS = "bin.xu@2021"
table = 'PDB'

db = MysqlDB(ip=MYSQL_IP, port=MYSQL_PORT, db=MYSQL_DB, user_name=MYSQL_USER_NAME, user_pass=MYSQL_USER_PASS)

# fp = r'D:\Data\projects\ProjectDeepBio\pdb_download\jsonfile'
# fp = r'\\192.168.1.33\share\CrawlerDownloads\personal\XuBin\pdb_download\jsonfile'
fp = r'D:\Data\projects\ProjectDeepBio\pdb_download\script'
file_path = get_file_path_list(fp, 'json')
for path in file_path:
    try:
        dict_obj = read_file(path)
        data = get_data(dict_obj, path)
        try:
            db.add_smart(table, data)
        except ValueError:
            log.error(f'{data},ValueError')
            pass
    except json.decoder.JSONDecodeError:
        log.error(f'{path},is error')
